import pandas as pd
import numpy as np
import os
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
from datetime import datetime, timedelta

# 设置中文显示
plt.rcParams['font.sans-serif'] = ['SimHei']  # 使用黑体
plt.rcParams['axes.unicode_minus'] = False  # 解决负号显示问题

EXCEL_PATH = "E:/M/MathModel/23国赛C题/ds/历年6月底到7月初.xlsx"
OUTPUT_DIR = "E:/M/MathModel/23国赛C题/ds/销售分析图表"


def load_and_prepare_data(file_path):
    df_2020 = pd.read_excel(file_path, sheet_name='2020')
    df_2021 = pd.read_excel(file_path, sheet_name='2021')
    df_2022 = pd.read_excel(file_path, sheet_name='2022')
    df_2023 = pd.read_excel(file_path, sheet_name='2023')

    # 合并数据
    df = pd.concat([df_2020, df_2021, df_2022, df_2023])

    # 转换日期格式
    df['销售日期'] = pd.to_datetime(df['销售日期'])

    return df


def calculate_historical_avg_prices(df):
    # 按品类分组计算平均批发价和平均销售单价
    avg_prices = df.groupby('分类名称').agg(
        avg_wholesale_price=('批发价', 'mean'),
        avg_retail_price=('销售单价(元/千克)', 'mean')
    ).reset_index()

    return avg_prices


def predict_prices(forecast_df, loss_rates, df):
    # 计算历史平均批发价和销售单价
    avg_prices = calculate_historical_avg_prices(df)

    price_df = pd.DataFrame(index=forecast_df.index, columns=forecast_df.columns)

    # 假设修正系数W=1
    W = 1.0

    # 对每个品类应用定价公式
    for category in forecast_df.columns:
        # 获取该品类的历史平均批发价
        if category in avg_prices['分类名称'].values:
            J = avg_prices[avg_prices['分类名称'] == category]['avg_wholesale_price'].values[0]
            S = avg_prices[avg_prices['分类名称'] == category]['avg_retail_price'].values[0]
        else:
            print(f"警告: 品类 '{category}' 无历史价格数据，使用默认值")
            J = 10.0
            S = 15.0

        # 获取损耗率
        K = loss_rates.get(category, 10.0) / 100.0

        # 应用定价公式
        for date in forecast_df.index:
            price = (J + K * J) * (1 + W * (S - J) / J)
            price_df.loc[date, category] = price

    return price_df.round(2)


def main():
    # 创建输出目录
    os.makedirs(OUTPUT_DIR, exist_ok=True)

    print("加载和预处理数据...")
    try:
        df = load_and_prepare_data(EXCEL_PATH)
        print(f"加载成功，共 {len(df)} 条记录")
    except Exception as e:
        print(f"数据加载失败: {e}")
        return

    forecast_data = {
        '花叶类': [0.44, 0.43, 0.42, 0.43, 0.42, 0.44, 0.44],
        '花菜类': [0.52, 0.51, 0.49, 0.49, 0.51, 0.51, 0.53],
        '辣椒类': [0.15, 0.16, 0.15, 0.14, 0.15, 0.14, 0.13],
        '茄类': [0.52, 0.51, 0.57, 0.50, 0.52, 0.48, 0.50],
        '水生根茎类': [0.58, 0.62, 0.66, 0.57, 0.64, 0.65, 0.62],
        '食用菌': [0.21, 0.17, 0.15, 0.16, 0.19, 0.12, 0.09]
    }

    # 创建预测DataFrame
    forecast_dates = pd.date_range('2023-07-08', periods=7, freq='D')
    forecast_df = pd.DataFrame(forecast_data, index=forecast_dates)

    # 损耗率
    avg_loss_rates = {
        '花叶类': 13.40,
        '花菜类': 10.86,
        '辣椒类': 7.83,
        '茄类': 6.07,
        '水生根茎类': 17.43,
        '食用菌': 8.56
    }

    # 预测售价
    print("使用成本加成定价法预测售价...")
    price_df = predict_prices(forecast_df, avg_loss_rates, df)

    # 保存结果
    print("保存结果...")
    try:
        # 保存为Excel
        output_path = os.path.join(OUTPUT_DIR, 'pricing_results.xlsx')
        with pd.ExcelWriter(output_path) as writer:
            forecast_df.to_excel(writer, sheet_name='销量预测')
            price_df.to_excel(writer, sheet_name='售价预测')

            # 添加损耗率信息
            loss_rate_df = pd.DataFrame({
                '品类': list(avg_loss_rates.keys()),
                '平均损耗率(%)': list(avg_loss_rates.values())
            })
            loss_rate_df.to_excel(writer, sheet_name='损耗率信息', index=False)

        print(f"Excel文件保存成功: {output_path}")
    except Exception as e:
        print(f"Excel保存失败: {e}")

    # 可视化
    print("步骤5: 生成可视化图表...")
    try:
        # 售价预测图
        plt.figure(figsize=(14, 8))
        for category in price_df.columns:
            plt.plot(price_df.index, price_df[category], 'o-', label=category)

        plt.title('2023年7月8-14日售价预测')
        plt.xlabel('日期')
        plt.ylabel('售价 (元/千克)')
        plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%m-%d'))
        plt.legend()
        plt.grid(True, linestyle='--', alpha=0.7)
        plt.tight_layout()

        plot_path = os.path.join(OUTPUT_DIR, 'price_forecast.png')
        plt.savefig(plot_path)
        plt.close()
        print(f"售价预测图保存成功: {plot_path}")

        # 品类价格对比图
        avg_prices = price_df.mean().sort_values(ascending=False)

        plt.figure(figsize=(10, 6))
        avg_prices.plot(kind='bar', color='skyblue')
        plt.title('各品类平均预测售价对比')
        plt.xlabel('品类')
        plt.ylabel('平均售价 (元/千克)')
        plt.xticks(rotation=45)
        plt.grid(axis='y', linestyle='--', alpha=0.7)
        plt.tight_layout()

        plot_path = os.path.join(OUTPUT_DIR, 'price_comparison.png')
        plt.savefig(plot_path)
        plt.close()
        print(f"价格对比图保存成功: {plot_path}")

    except Exception as e:
        print(f"图表生成失败: {e}")


    # 输出
    print("\n023年7月8-14日预测结果")
    print("销量预测:")
    print(forecast_df)

    print("\n售价预测 (元/千克):")
    print(price_df)

    print("\n平均损耗率:")
    for category, rate in avg_loss_rates.items():
        print(f"  - {category}: {rate:.2f}%")

    print(f"\n所有结果已保存至: {os.path.abspath(OUTPUT_DIR)}")


if __name__ == "__main__":
    main()